import xlrd
import xlsxwriter
from getName import get_table_name


class ReadXls(object):
    def __init__(self):
        self.t_data = {}
        self.all_list = []

    # def read_xls_head(self, filepath):
    #     data = xlrd.open_workbook(filepath)
    #     names = data.sheet_names()
    #
    #     for name in names:
    #         t_common_header = []
    #         table = data.sheet_by_name(name)
    #         data.sheet_loaded(name)
    #
    #         """取公共表头"""
    #         t_common_header.append(table.row_values(0))
    #         t_common_header[-1].insert(0, "档号")
    #
    #         for i in range(len(t_common_header[0])):
    #             t_common_header[0][i] = t_common_header[0][i].replace(" ", "")
    #
    #         self.t_data["common_header"] = t_common_header
    #         self.all_list.append(self.t_data)
    #         return self.all_list

    def read_xls_head(self):
        t_common_header = [['档号', '序号', '文件编号', '责任者', '文件材料题名', '日期', '页次', '备注']]
        self.t_data["common_header"] = t_common_header
        self.all_list.append(self.t_data)
        return self.all_list

    def read_xls_body(self, filepath):
        data = xlrd.open_workbook(filepath)
        names = data.sheet_names()
        self.t_data = {}

        for name in names:
            sheet_data = []
            table = data.sheet_by_name(name)
            nrows = table.nrows
            ncols = table.ncols

            """取数据"""
            for nrow in range(nrows):
                if table.row_values(nrow)[0] == "序号":
                    nrow += 1
                    continue
                elif table.row_values(nrow)[0] == "卷  内  目  录":
                    nrow += 1
                    continue
                elif table.row_values(nrow)[0] == "归  档  文  件  目  录":
                    nrow += 2
                    continue
                elif table.row_values(nrow)[0] == "件号":
                    nrow += 1
                    continue
                elif table.row_values(nrow)[0] in ["", " "]:
                    for ncol in range(ncols):
                        if table.row_values(nrow)[ncol] not in ["", " "]:
                            # 遇到空sheet就跳过
                            if len(sheet_data) == 0:
                                continue

                            sheet_data[-1][ncol + 1] += str(table.row_values(nrow)[ncol])

                    nrow += 1
                    continue

                # 遇到相同行跳过
                temp_list = table.row_values(nrow)
                temp_list.insert(0, name)
                if temp_list in sheet_data:
                    nrow += 1
                    continue

                # print(table.row_values(nrow))
                sheet_data.append(table.row_values(nrow))
                sheet_data[-1].insert(0, name)
                for i in sheet_data:
                    for j in range(len(i)):
                        i[j] = str(i[j])
                nrow += 1

            self.t_data[name] = sheet_data

        self.all_list.append(self.t_data)
        return self.all_list

    def save_to_file(self, result):
        f = xlsxwriter.Workbook("result.xlsx")
        sheet = f.add_worksheet()

        sheet.set_column("C:D", 25)
        sheet.set_column("E:E", 40)
        sheet.set_column("F:F", 15)

        body_format = f.add_format({
            'align': 'center',
            'valign': 'vcenter',
            'border': 1,
            'text_wrap': 1
        })

        r = 0
        for i in result:
            for j in i.values():
                for k in j:
                    c = 0
                    for l in k:
                        sheet.write(r, c, l, body_format)
                        sheet.set_row(r, 30)
                        c += 1
                    r += 1

        f.close()


if __name__ == "__main__":
    bg = ReadXls()

    bg.read_xls_head()
    for file in get_table_name():
        result = bg.read_xls_body("./" + file)
        bg.save_to_file(result)
